序言
MySQL 从 3.23 版本开始提供复制的功能。
复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到复制服务器（也叫从库）上，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。
MySQL 支持一台主库同时向多台从库进行复制，从库同时也可以作为其他服务器的主库， 实现链状的复制。
MySQL 复制的优点主要包括以下三个方面：
- 高可用：如果主库出现问题，可以快速切换到从库提供服务；
- 负载均衡：读写分离使得可以在从库上执行查询操作，降低主库的访问压力；
- 数据安全：可以在从库上执行备份，以避免备份期间影响主库的服务
工作原理
MySQL 的复制原理大致如下：
- ① 首先，MySQL 主库在事务提交时会把数据变更作为事件 Events 记录在二进制日志文件 Binlog中；MySQL主库上的 sync_binlog 参数控制 Binlog 日志刷新到磁盘。
- ② 主库推送二进制日志文件 Binlog 中的事件到从库的中继日志 Relay Log，之后从库根据中继日志 Relay Log 重做数据变更操作， 通过逻辑复制以此来达到主库和从库的数据一致。
MySQL通过 3 个线程来完成主从库间的数据复制： 其中 Binlog Dump 线程运行在主库上， I/O 线程和 SQL 线程运行在从库上。
当在从库上执行START SLAVE命令启动复制时，首先创建 I/O 线程连接主库，主库随后创建 Binlog Dump 线程读取数据库事件并发送给 IO 线程，I/O 线程获取到事件数据后更新到从库的中继日志 Relay Log 中去，之后从库上的 SQL 线程读取中继日志 Relay Log 中更新的数据库事件并应用。
复制方式
MySQL 的主从复制方式，共存在异步复制、半同步复制、全同步复制（组复制）三种，下面分别介绍。
异步复制
异步复制是 MySQL 的默认复制方式，其原理就是主库写入 binlog 日志后即可成功返回给客户端，不用等待 binlog 日志传递给从库。这样就会出现一个问题，假如主机 crash 掉了，主库上提交的事务可能并没有传到从库，这样就会造成数据丢失的情况。
步骤
异步复制配置的步骤比较简单，下面是详细步骤：
- ① 确保主从库上安装了相同版本的数据库
② 在主库设置一个复制使用的账户，并授予
REPLICATION SLAVE权限。这里创建一个复制用户repl， 可以从IP为10.211.55.27的主机进行连接：1
GRANT REPLICATION SLAVE ON*.* To 'repl'@'10.211.55.27' IDENTIFIED BY '123456replicate';
③ 修改主库的配置文件
my.cnf，开启binlog，并设置server-id的值：1
2
3
4[mysqld]
server-id=1
log-bin =binlog
binlog_format=ROW④ 重新启动主库数据库服务使配置生效：
1
service mysql restart
⑤ 在主库上，设置读锁定有效，这个操作是为了确保没有数据库操作产生新数据，以便获得一个一致性的快照:
1
FLUSH TABLES WITH READ LOCK;
⑥ 记录下主库当前的二进制日志名和偏移量值。这个操作的目的是为了在从库启动以后，从这个点开始进行数据的恢复。
1
2
3
4
5
6
7mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 | 1487 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)⑦ 现在主库已经停止了更新操作， 需要生成主库的备份，备份的方式有很多种，这里我们通过
mysqldump导出主库的备份（数据量大的时候建议使用数据迁移工具如 DataX，以缩短数据迁移时间）：1
2
3
4
5
6
7
8
9# 导出所有数据库
mysqldump -uroot -p --all-databases > ./all.sql
# 导出部分数据库
mysqldump -uroot -p --databases db1 db2 > ./db1db2.sql
# 压缩数据库文件
tar -czf sql.tar.gz all.sql
# 传输到从库指定目录
scp sql.tar.gz root@10.211.55.27:/var/lib/mysql⑧ 主库的备份完毕后，可以恢复写操作，剩下的操作只需要在从库上执行:
1
UNLOCK TABLES;
⑨ 将主库刚刚保存传输的一致性备份文件在从库上恢复：
1
2
3
4cd /var/lib/mysql
tar -zxvf sql.tar.gz
mysql -uroot -p
source all.sql⑩ 修改从库的配置文件
my.cnf，增加server-id参数：1
2
3
4[mysqld]
server-id=2
log-bin =binlog
binlog_format=ROW⑪ 重新启动从库数据库服务使配置生效：
1
service mysql restart
⑫ 对从库做相应设置，指定复制使用的用户，主库的 IP 及端口以及开始执行复制的日志文件和位置等，命令示例如下:
1
CHANGE MASTER TO MASTER_HOST='master_host_name’, MASTER_PORT=3306, MASTER_USER='username', MASTER_PASSWORD='[pwd]', MASTER_LOG_FILE='recorded_log_position_file', MASTER_LOG_POS=recorded_log_position;
举例说明如下：1
CHANGE MASTER TO MASTER_HOST='10.211.55.26', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='123456replicate', MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=1487;
⑬ 在从库启动
slave线程，开始同步主库数据:1
START SLAVE;
⑭ 这时
slave上执行SHOW SLAVE STATUS\G;命令将显示如下结果：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64# 等待主库发送数据
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.211.55.26
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000003
Read_Master_Log_Pos: 74053540
Relay_Log_File: mysql_slave-relay-bin.000002
Relay_Log_Pos: 74052370
Relay_Master_Log_File: binlog.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 74053540
Relay_Log_Space: 74052583
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 75d25b61-dbe0-11ec-9bea-001c42d73146
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
这表明slave已经连接上master，并开始接受并执行日志。
至此，异步复制搭建完成。
测试
为了测试复制服务的正确性，可以首先在主库上执行一个更新操作，然后在从库观察数据是否同步。
1 | # 主库修改数据时，从库接收 |
若可以看到数据可以正确同步到从数据库上，则代表异步复制搭建成功。
总结
注意：由于 MySQL 实现的是异步的复制，所以主从库之间存在一定的差距，在从库上进行的查询操作需要考虑到这些数据的差异，一般只有更新不频繁的数据或者对实时性要求不高的数据可以通过从库查询，实时性要求高的数据仍然需要从主数据库获得。
MySQL 主从异步复制是最常见和最简单的复制场景，复制的三个线程Binlog Dump、I/O、SQL之间都是独立的，相互之间没有依赖关系。数据的完整性完全依赖于主库的Binlog的不丢失，只要主库的Binlog不丢失，那么就算主库宕机了，我们还可以通过Binlog把丢失的部分数据通过手工同步到从库上去。
MySQL 在事务（或SQL语句 ）提交但尚未释放锁的时候，在Binlog中记录事务（或SQL语句）， 也就是说对于支持事务的引擎（例如 InnoDB 来说，每个事务提交时都需要写Binlog； 对于不支持事务的引擎（例如 MyISAM）来说，每个 SQL语句执行完成时，都需要写 Binlog。 为了保证 Binlog 的安全，MySQL引人sync_ binlog参数来控制Binlog刷新到磁盘的频率：1
2
3
4
5
6
7mysql> SHOW VARIABLES LIKE '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog | 1 |
+---------------+-------+
1 row in set (0.00 sec)
对sync_binlog的取值而言：
sync binlog=0：表示 MySQL 不控制Binlog的刷新，由文件系统自己控制文件系统的缓存的刷新sync binlog>0：表示每多少次事务提交，MySQL 调用文件系统的刷新操作将Binlog刷到磁盘。- 比如
sync binlog=1（默认值），表示每一次事务提交，MySQL 都需要把Binlog刷新到磁盘，这样的话，数据库主机的操作系统崩溃或者主机突然掉电的情况下，系统最多损失最近的一个事务的数据（因为上一次的事务提交的时候已经把Binlog刷新到磁盘，当前事务提交时Binlog有可能没有刷新到磁盘，也可能已经刷新到磁盘）
- 比如
设置sync binlog=1可以尽最大可能保证数据安全，但是在多个事务并发提交时，sync binlog=1使得 MySQL 不得不按顺序处理请求，同时高频率的刷新binlog对 IO 的影响明显，很大的影响了 MySQL 的性能。
所以一般线上系统 MySQL 的sync_ binlog不会设置为最安全的1，而是其他值或者是0，在数据安全性和更高的并发和性能中间获取一个平衡。
半同步复制
如果等待超时，超过rpl_semi_sync_master_timeout参数设置的时间，则关闭半同步复制，并自动转换为异步复制模式，直到至少有一台从库通知主库已经接收到了binlog为止。
半同步复制通过rpl_semi_sync_master_wait_point参数来控制master在哪个环节接收到slave的ack后返回状态给客户端。该参数存在两个值：
AFTER_COMMIT：该参数是MySQL5.6版本的默认值，表示主库将事务写入binlog,并传递给从库，同时主库提交该事务，只有收到从库返回ack的时候，才将成功结果反馈给客户端。在等待从库ack的时候虽然没有给客户端反馈，但是事务已经提交，其他客户端会读取到该事务。如果slave端还没有读取到该事务的events,同时主库发生crash，然后切换到了备库，那么之前已经在主库提交的时候就不见了，这时就出现了数据不一致的问题，如果主库用于启动不了了，那么该事务也就丢失了。AFTER_SYNC：针对上面提到的问题，MySQL 5.7 版本又引入了此参数，该模式下主库会等到至少有 N 个从库接收了 binlog 并反馈 ack 之后才提交事务。N 由rpl_semi_sync_master_wait_for_slave_count控制
after_sync模式下解决了AFTER_COMMIT模式下带来的数据不一致的问题，因为主库只有等到从库的ack之后才提交事务。但是同样还有一个问题，当主库的binlog同步到从库之后，binlog sync之前发生了crash，很明显这个事务在主库上未提交成功，但是从库已经接收了这些binlog，并且执行成功，如果发生了主从切换，相当于从库多出了数据，而主库已经回滚掉了，这时候就又造成了主从的数据”不一致”。
步骤
半同步复制依赖 MySQL5.5 版本后新增的一个插件来进行实现的，主库和从库需使用不同的插件。
半同步复制安装也比较简单，在异步复制的基础上，安装半同步复制插件即可。
① 首先，执行以下命令检查 MySQL 服务器是否支持动态增加插件（
YES代表支持）：1
2
3
4
5
6
7mysql> SELECT @@have_dynamic_loading;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES |
+------------------------+
1 row in set (0.00 sec)② 确认支持动态增加插件后，检查 MySQL 的安装目录下是否存在插件，一般默认在
$MYSQL_HOME/lib/plugin目录下存在主库插件semisync_master.so和从库插件semisyncslave.so- ③ 确认插件存在后，在主从库分别安装对应插件并确认是否安装成功：
1
2
3
4
5
6
7
8
9
10-- 在主库上安装插件 semisync master.so:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
-- 查询 plugin 表确认是否安装成功
mysql> SELECT * FROM mysql.plugin;
+----------------------+--------------------+
| name | dl |
+----------------------+--------------------+
| rpl_semi_sync_master | semisync_master.so |
+----------------------+--------------------+
1 row in set (0.00 sec)
1 | -- 在从库上则安装 semisync_slave.so插件: |
- ④ 默认半同步设置是不打开的，因此需要分别在主库和从库上配置参数打开半同步
1
2
3
4
5-- 主库上配置全局参数：
mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL rpl_semi_sync_master_timeout = 30000;
Query OK, 0 rows affected (0.00 sec)
1 | -- 从库上一样配置全局参数 |
- ⑤ 注意，由于之前配置的复制是异步复制，所以需要重启一下从库上的 I/O 线程
1
2
3
4
5mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)
至此，半同步配置完毕。
测试
下面可以通过不同场景来验证一 下。
正常场景
主库上执行以下命令查看半同步复制的一些状态值：1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19mysql> SHOW STATUS LIKE '%semi_sync%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 3 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
着重关注以下三个状态值。
Rpl_semi_sync_master_status：值为 ON， 表示半同步复制目前处于打开状态。Rpl_semi_sync_master_yes_tx：值为 0，表示主库当前尚未有任何一个事务是通过半
同步复制到从库Rpl_semi_sync_master_no_tx：值为 3， 表示当前有 3 个事务不是半同步模式下从库及
时响应的（记住这个值，后面会有对比）
执行一个事务，再检查一下状态：
此时会发现Rpl_semi_sync_master_yes_tx的值变为 1，即刚才的 INSERT 事务通过半同步复制到从库上了，到从库确认一下，数据确实被复制过去了。
网络异常场景
再模拟网络异常的场景，主库在等待rpl semi sync master timeout毫秒超时后，自动转成异步复制的场景。
- ① 首先，在主库上确认半同步复制会等待 30s 超时：
- ② 在从库上通过
iptables命令模拟从库宕机或者网络故障： - ③ 在主库上执行一个事务并提交（默认提交即可），主库上的提交操作会被阻塞 30 秒：
新开一个窗口检查当前主库的线程，会发现提交操作在等待从库上半同步复制操作作的响应： - ④ 主库上等待 30s 超时:
检查半同步复制的一些状态值，仍然看之前着重关注的3个状态值。
Rpl_semi_sync_master_status：值变为 OFF 了， 表示主库上半同步复制已经关闭了， 目前复制模式为异步复制；Rpl_semi_sync_master_yes_tx：值仍然为 1，表示刚才的事务并不是通过半同步复制完成的，所以半同步成功事务仍然为 1， 并不累加Rpl_semi_sync_master_no_tx：值更新为 4， 比原来的 3 累加了 1，表示在半同步模式 下，从库没有及时响应的事务增加 1 个
网络恢复场景
继续确认下一个场景：从库正常连接上主库之后，确认主库是否会自动切换回半同步复制模式。
- ① 在之前的场景下，从库上取消
iptables过滤规则:
检查从库上的I/O线程的状态，去掉iptables规则之后，从库会自动尝试连接主库，几秒钟后I/O线程的状态从Connecting变成了YES，并且主库和从库的数据一致了： - ② 检查主库上半同步复制的状态值，发现Rpl semi sync master status 的值自动从OFF变成ON，说明在检测到从库正常之后，主库到从库的复制方式会自动切换为半同步复制模式：
- ③ 在主库上做一个测试，确认当前的复制模式确实是半同步复制：
提交-个事务之后，Rpl semi sync master yes tx 从1变成2，确认刚才事务的复制是半同步复制。
总结
从半同步复制的流程会发现， 半同步复制的“半”就体现在：虽然主库和从库的 Binlog 日志是同步的，但是主库并不等待从库应用这部分日志就返回提交结果，这部分操作是异步的，从库的数据并不是和主库实时同步的，所以只能称为半同步，而不是完全的实时同步。
全同步复制
全同步复制时，主库在所有从库同步完成后才返回提交结果，这需要部署 MGR 集群，具体见 MySQL 高可用架构——MGR 搭建一文。
差异性
MySQL 的异步复制和半同步复制至少需要两个 MySQL 服务，全同步复制至少需要三个 MySQL 服务。
主从延迟现象
主从延迟指的是在主库执行成功的数据，由于复制存在一定时间，在从库上需要过一段时间才执行成功。
来源
- 备库读压力大
- 大事务场景
- 从库的并行复制能力
- 从机机器性能比主机机器差
适配方案
针对主从延迟，若运维短时间内无法解决问题，导致业务出现问题，可以考虑以下方案解决问题：
- ① 从代码层面避免：代码最好不要有先写后立即就要读相同数据的操作（比如先插入一条数据，在查出来这条数据，再更新这条数据，这里就可能存在插入成功后，由于从库延迟，导致从从库根据
id查出来的为null，然后再按id更新这条数据就是where id = null的情况，这会出现问题） - ② 直连主库：若写完就读的操作代码无法避免，可以设置查询的时候直连主库，从主库查数据，这个可用借助
ShardingJDBC比较新的版本（5.1 之后）自动实现
解决方案
针对主从延迟，解决方案存在多种：
- ① 启用并行复制：在 MySQL 5.7 之后可以用并行复制解决主从同步延时问题，所谓并行复制，指的是从库开启多个 SQL 线程，并行读取
relay log中不同库的日志，然后并行重放不同库的日志，这是库级别的并行，如果单库的并发很高的话也不行，但是打开肯定比不打开好 - ② 考虑分库：大多数情况主从延迟是因为主库的写并发太高，从库同步不过来，若进行分库，则每个库压力就小了，主从延迟时间可以大大降低
当然能不分库还是别分库，所以我们优先选择开启 MySQL 的并行复制功能。
那么，如何开启 MySQL 的并行复制功能呢？
待补充。
主从不一致
主从数据不一致可能是由多种因素造成的，常见的数据不一致，从目前了解的一些案例总结如下：
- 从库被写入数据
- binlog 非 row 格式
- 使用了存储过程或触发器
- sql_mode 不一致
- 跳过了复制数据
- 双 1 设置
- 采用半同步机制
- sql_log_bin参数
- 主从表结构不一致
- 备份导致一致性丢失
- 版本不一致
复制中断现象
主从复制在生产环境下，有时候会出现中断现象，这是由于异常导致的。
出现异常后，可在从机通过SHOW SLAVE STATUS\G;显示详细错误信息，信息中可观察到相应错误码，复制中断常见错误码及说明见下表：
| 错误码 | 说明 |
|---|---|
| 1007 | 数据库已存在，创建数据库失败 |
| 1008 | 数据库不存在，删除数据库失败 |
| 1050 | 数据表已存在，创建数据表失败 |
| 1051 | 数据表不存在，删除数据表失败 |
| 1053 | 复制过程中主服务器宕机 |
| 1054 | 字段不存在，或程序文件跟数据库有冲突 |
| 1060 | 字段重复，导致无法插入 |
| 1061 | 重复键名 |
| 1062 | 主键冲突 Duplicate entry ‘%s’ for key %d |
| 1068 | 定义了多个主键 |
| 1094 | 位置线程ID |
| 1146 | 数据表缺失，请恢复数据库 |
解决的办法是在从库上执行以下命令跳过相关错误:
1 | stop slave; |
当然亦可在my.ini配置文件[mysqld]下增加以下配置指定想要跳过的错误码：1
slave_skip_errors = 1062
踩坑记录
异常一：密码强度过低导致主从复制中断
生产环境客户自己创建新用户，由于设置密码强度过低，引起主从复制中断问题，查阅官方文档 MySQL 确实存在此 Bug
解决方案
将密码强度校验规则设置简单一点：1
2
3
4# 只验证密码强度
SET GLOBAL validate_password_policy=LOW;
# 设置密码长度为 4
SET GLOBAL validate_password_length=4;
异常二：从库删除不存在的数据导致主从复制中断
1 | Last_SQL_Error: Could not execute Delete_rows event on table database_bus.trace_operate_log; |
这个错误具体描述就是：从库想删的数据被提前删除了，比如：
- ① 执行过人工操作（从库先执行删除操作，主库再执行删除操作，从库回放主库的删除命令发现没有数据了，那么删除报错）
- ② 从库初始化时忽略了部分表：比如主从出现过中断现象，为了恢复主从，对主库进行锁表导出所有库但忽略了部分表（如数据较大的日志表），之后在从库进行恢复。由于日志表一般会进行定期删除，若定时任务执行删除的数据在主库有，而从库没有（从库的日志表确实没有，因为还原的备份脚本中就没有），那么也会出现这种错误
解决方案
若遇上问题 ① ，首先可以在从库通过以下方式跳过此错误，之后在从库设置只读模式禁止普通用户进行修改操作，或者从库不开放给开发者避免出现开发用错了数据库：1
2
3stop slave;
set global sql_slave_skip_counter=1;
start slave;
若遇上问题 ②，可以：
- 在主库删除进行数据备份前的日志，之后从库执行前面的 SQL 跳过一次此错误
- 在主库导出备份前的日志，然后在从库重新执行，这样主从的日志表就保持一致了，别忘了从库需要再执行一次前面的 SQL 跳过此错误
参考
- 唐汉明. 深入浅出 MySQL 数据库开发、优化与管理维护（第2版）[M]. 人民邮电出版社, 2014.1
文章信息
| 时间 | 说明 |
|---|---|
| 2022-02-22 | 初稿 |
| 2022-08-20 | 微调 |
| 2022-10-30 | 重构 |